from flask import Flask, jsonify, make_response
import pymysql
from decimal import Decimal
import logging
from db_config import DB_CONFIG
from sqls import SQLS

# 配置日志
logging.basicConfig(level=logging.INFO, format='%(asctime)s - %(levelname)s - %(message)s')
logger = logging.getLogger(__name__)

app = Flask(__name__)

def convert_decimal(obj):
    if isinstance(obj, Decimal):
        return float(obj)
    if isinstance(obj, dict):
        return {k: convert_decimal(v) for k, v in obj.items()}
    if isinstance(obj, list):
        return [convert_decimal(i) for i in obj]
    return obj

def query_db(sql):
    logger.info(f"执行SQL: {sql}")
    try:
        conn = pymysql.connect(**DB_CONFIG)
        logger.info("数据库连接成功")
        cursor = conn.cursor()
        cursor.execute(sql)
        rows = cursor.fetchall()
        logger.info(f"查询到 {len(rows)} 行数据")
        columns = [desc[0] for desc in cursor.description]
        logger.info(f"字段名: {columns}")
        result = []
        for i, row in enumerate(rows):
            row_dict = dict(zip(columns, row))
            # 对所有 Decimal 字段进行转换
            for key, value in row_dict.items():
                if isinstance(value, Decimal):
                    row_dict[key] = float(value)
            result.append(row_dict)
            if i < 3:  # 只打印前3行数据用于调试
                logger.info(f"第{i+1}行数据: {row_dict}")
        cursor.close()
        conn.close()
        logger.info("数据库连接已关闭")
        return result
    except Exception as e:
        logger.error(f"数据库查询出错: {str(e)}")
        return []

@app.route('/api/customer_ranking')
def customer_ranking():
    logger.info("收到客户排名接口请求")
    try:
        data = query_db(SQLS['customer_ranking'])
        logger.info(f"客户排名数据: {len(data)} 条")
        
        # 验证数据格式
        if not isinstance(data, list):
            logger.error("查询结果不是列表格式")
            data = []
        
        # 验证每条数据的字段
        valid_data = []
        for i, row in enumerate(data):
            if isinstance(row, dict):
                # 确保必要字段存在
                required_fields = ['用户姓名', '收费重', '实际重量', 'CBM', '泡比']
                missing_fields = [field for field in required_fields if field not in row]
                if missing_fields:
                    logger.warning(f"第{i+1}行缺少字段: {missing_fields}")
                    continue
                
                # 确保数值字段是数字
                try:
                    row['收费重'] = float(row['收费重']) if row['收费重'] is not None else 0
                    row['实际重量'] = float(row['实际重量']) if row['实际重量'] is not None else 0
                    row['CBM'] = float(row['CBM']) if row['CBM'] is not None else 0
                    row['泡比'] = float(row['泡比']) if row['泡比'] is not None else 0
                    valid_data.append(row)
                except (ValueError, TypeError) as e:
                    logger.warning(f"第{i+1}行数据转换失败: {e}")
                    continue
            else:
                logger.warning(f"第{i+1}行不是字典格式: {type(row)}")
        
        logger.info(f"有效数据: {len(valid_data)} 条")
        if valid_data:
            logger.info(f"第一条数据: {valid_data[0]}")
        
        response = make_response(jsonify(valid_data))
        response.headers['Access-Control-Allow-Origin'] = '*'
        logger.info("客户排名接口响应完成")
        return response
        
    except Exception as e:
        logger.error(f"客户排名接口处理出错: {str(e)}")
        response = make_response(jsonify([]))
        response.headers['Access-Control-Allow-Origin'] = '*'
        return response

@app.route('/api/channel_ratio')
def channel_ratio():
    logger.info("收到渠道占比接口请求")
    data = query_db(SQLS['channel_ratio'])
    logger.info(f"渠道占比原始数据: {data}")
    # 确保 data 是列表
    if not isinstance(data, list):
        data = []
    # 处理 name 为空
    for row in data:
        if not row.get('name'):
            row['name'] = '其他'
    # 计算总和
    total = sum(float(row.get('value', 0)) for row in data) or 1  # 防止除0
    logger.info(f"渠道占比总和: {total}")
    # 组装返回：value为原始数值，percent为百分比
    for row in data:
        raw_value = float(row.get('value', 0))
        row['percent'] = round(raw_value / total * 100, 2)
        row['value'] = int(raw_value) if raw_value == int(raw_value) else raw_value
    logger.info(f"渠道占比处理后数据: {data}")
    response = make_response(jsonify(data))
    response.headers['Access-Control-Allow-Origin'] = '*'
    logger.info("渠道占比接口响应完成")
    return response

@app.route('/api/data_statistic')
def data_statistic():
    logger.info("收到数据统计接口请求")
    try:
        data = query_db(SQLS['data_stat'])
        logger.info(f"数据统计原始数据: {data}")
        # 构建返回格式
        xAxis = []
        weight = []
        volume = []
        parcel_count = []
        for row in data:
            xAxis.append(row.get('week', ''))
            weight.append(float(row.get('weight', 0)))
            volume.append(float(row.get('volume', 0)))
            parcel_count.append(int(row.get('parcel_count', 0)))
        result = {
            'xAxis': xAxis,
            'weight': weight,
            'volume': volume,
            'parcel_count': parcel_count
        }
        response = make_response(jsonify(result))
        response.headers['Access-Control-Allow-Origin'] = '*'
        logger.info("数据统计接口响应完成")
        return response
    except Exception as e:
        logger.error(f"数据统计接口处理出错: {str(e)}")
        response = make_response(jsonify({'xAxis': [], 'weight': [], 'volume': [], 'parcel_count': []}))
        response.headers['Access-Control-Allow-Origin'] = '*'
        return response

@app.route('/api/risk_alerts')
def risk_alerts():
    logger.info("收到风险预警接口请求")
    try:
        data = query_db(SQLS['risk_alerts'])
        logger.info(f"风险预警原始数据: {data}")
        result = []
        for i, row in enumerate(data):
            name = row.get('username', '')
            week = row.get('weight', 0)
            last = row.get('lastweight', 0)
            ratio = row.get('increase_ratio', 0)
            try:
                percent = float(ratio) * 100
                percent_str = f"{percent:.1f}%"
            except Exception:
                percent_str = "0.0%"
            result.append({
                'idx': i+1,
                'name': name,
                'week': week,
                'last': last,
                'rate': {'value': percent_str, 'badge': ''}
            })
        response = make_response(jsonify(result))
        response.headers['Access-Control-Allow-Origin'] = '*'
        logger.info("风险预警接口响应完成")
        return response
    except Exception as e:
        logger.error(f"风险预警接口处理出错: {str(e)}")
        response = make_response(jsonify([]))
        response.headers['Access-Control-Allow-Origin'] = '*'
        return response

@app.route('/api/customer_count_week')
def customer_count_week():
    logger.info("收到发货客户数（按周）接口请求")
    try:
        data = query_db(SQLS['customer_count_week'])
        logger.info(f"发货客户数原始数据: {data}")
        xAxis = []
        yData = []
        for row in data:
            xAxis.append(row.get('xAxis', ''))
            yData.append(int(row.get('data', 0)))
        result = {
            'xAxis': xAxis,
            'data': yData
        }
        response = make_response(jsonify(result))
        response.headers['Access-Control-Allow-Origin'] = '*'
        logger.info("发货客户数（按周）接口响应完成")
        return response
    except Exception as e:
        logger.error(f"发货客户数（按周）接口处理出错: {str(e)}")
        response = make_response(jsonify({'xAxis': [], 'data': []}))
        response.headers['Access-Control-Allow-Origin'] = '*'
        return response

@app.route('/api/customer_count_month')
def customer_count_month():
    logger.info("收到发货客户数（按月）接口请求")
    try:
        data = query_db(SQLS['customer_count_month'])
        logger.info(f"发货客户数（按月）原始数据: {data}")
        xAxis = []
        yData = []
        for row in data:
            xAxis.append(row.get('month', ''))
            yData.append(int(row.get('count', 0)))
        result = {
            'xAxis': xAxis,
            'data': yData
        }
        response = make_response(jsonify(result))
        response.headers['Access-Control-Allow-Origin'] = '*'
        logger.info("发货客户数（按月）接口响应完成")
        return response
    except Exception as e:
        logger.error(f"发货客户数（按月）接口处理出错: {str(e)}")
        response = make_response(jsonify({'xAxis': [], 'data': []}))
        response.headers['Access-Control-Allow-Origin'] = '*'
        return response

# 测试接口
@app.route('/api/test')
def test():
    logger.info("收到测试接口请求")
    return jsonify({"status": "ok", "message": "后端服务正常运行"})

# 你可以继续添加其他接口
# @app.route('/api/other')
# def other():
#     data = query_db(SQLS['other_api'])
#     response = make_response(jsonify(data))
#     response.headers['Access-Control-Allow-Origin'] = '*'
#     return response

if __name__ == '__main__':
    logger.info("启动Flask服务...")
    app.run(host='0.0.0.0', port=5001, debug=True)